package mrtyp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;


public class SearchBean {
	
	public static void main(String [] args){
		SearchBean s = new SearchBean();
		s.search("Pizzerie","Sunday, hour = 18", "7.6719015", "45.1119611", "2000");
		s.search("Pizzerie","Wednesday, hour = 12", "7.6719015", "45.1119611", "2000");
	}
	
	public Map<String,Integer> DayTranslator = new HashMap<String,Integer>();
	{
		DayTranslator.put("Sunday",Calendar.SUNDAY);
		DayTranslator.put("Monday",Calendar.MONDAY);
		DayTranslator.put("Tuesday",Calendar.TUESDAY);
		DayTranslator.put("Wednesday",Calendar.WEDNESDAY);
		DayTranslator.put("Thursday",Calendar.THURSDAY);
		DayTranslator.put("Friday",Calendar.FRIDAY);
		DayTranslator.put("Saturday",Calendar.SATURDAY);
	}
	
	public String search(String category, String time, String clon, String clat, String radius) {
		StringBuffer sb = new StringBuffer();
		
		System.out.println(category);
		System.out.println(time);
		System.out.println(clon+","+clat+" radius = "+radius);
		
		//split time
		String dayofweek = time.split(",")[0];
		String hour = time.substring(time.length()-2);
		
		int d = DayTranslator.get(dayofweek);
		int h = Integer.parseInt(hour);
		
		/*
		int d = 0;
		int h = 0;
		
		
		Calendar cal = new GregorianCalendar();
		d = cal.get(Calendar.DAY_OF_WEEK);
		h = cal.get(Calendar.HOUR_OF_DAY);
		if(cal.get(Calendar.MINUTE) > 30) h++;
		
		*/
		
		System.out.println(d+" "+h);
		
		//get bounding box
		double cx = Double.parseDouble(clon);
		double cy = Double.parseDouble(clat);
		double r = Double.parseDouble(radius);
		double minlon = cx-0.1;
		double minlat = cy-0.1;
		double maxlon = cx+0.1;
		double maxlat = cy+0.1;
		
		String points = "'MULTIPOINT("+minlon+" "+minlat+","+maxlon+" "+minlat+","+maxlon+" "+maxlat+","+minlon+" "+maxlat+")'";
	    
		
		
		System.out.println("-----");
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		    Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/PagineGialle?user=root&password=root");
		    Statement s = c.createStatement();	
			
			String query = "SELECT h"+h+" AS r,name,address,city,state,AsText(coord) AS coord FROM paginegialle.Rank INNER JOIN paginegialle.Business ON Rank.idBusiness=Business.idBusiness WHERE Business.category='"+category+"' AND Rank.day = "+d+" AND (Intersects(coord,GeomFromText("+points+"))) ORDER BY h"+h+" DESC";
			System.out.println(query);
			ResultSet rs = s.executeQuery(query);
			
			int i = 0;
			while(rs.next()) {
				String rank = rs.getString("r");
				System.out.println(rank);
				String name = rs.getString("name");
				System.out.println(name);
		    	String address = rs.getString("address");
		    	String city = rs.getString("city");
		    	String state = rs.getString("state");
		    	String coord = rs.getString("coord");
		    	double lon  = Double.parseDouble(coord.substring(coord.indexOf("(")+1,coord.indexOf(" ")));
		    	double lat  = Double.parseDouble(coord.substring(coord.indexOf(" ")+1,coord.indexOf(")")));
				sb.append("<div onclick=\"hideshow("+i+")\" class=\"result\">"+name+"<img src=\"stuff/marker.png\"/><div class=\"address\">"+address+","+city+" ("+state+")</div></div>");
				sb.append("<iframe id=\""+i+"\" class=\"hide\" width=\"425\" height=\"350\" frameborder=\"0\" scrolling=\"no\" marginheight=\"0\" marginwidth=\"0\" src=\"http://maps.google.it/maps?ll="+lat+","+lon+"&output=embed\"></iframe>");
				i++;
				if(i > 10) break;
			}
			if(i==0) sb.append("<div class=\"result\">Not Found</div>");
			
			s.close();
		    c.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		return sb.toString();
	}
}
